package com.tcr.dao;

public class SqlString {

	// create table;
	public final static String crt_tc_user = "create table if not exists tc_user(uid integer primary key asc autoincrement,u_mail varchar(255) not null, u_pass varchar(255) not null, u_name varchar(255) ,u_type integer(1) default 0,u_upload integer(1) default 0);";
	public final static String crt_tc_proj = "create table if not exists tc_proj(pid integer primary key asc autoincrement,p_name varchar(255) not null,p_code integer(1),p_svn varchar(255),p_create_time TIMESTAMP not null default CURRENT_TIMESTAMP);";
	public final static String crt_tc_upmap = "create table if not exists tc_upmap(uid integer ,pid integer ,join_time TIMESTAMP not null default CURRENT_TIMESTAMP,primary key (uid,pid))";
	public final static String crt_tc_cov = "create table if not exists tc_cov(cid integer primary key asc autoincrement,pid integer,c_dir varchar(255) not null,c_version varchar(255) not null,uploader integer not null ,upload_time TIMESTAMP not null default CURRENT_TIMESTAMP ,FOREIGN KEY(pid) REFERENCES tc_proj(pid))";

	// user
	public final static String add_normal_user = "insert into tc_user (u_name,u_mail,u_pass,u_type,u_upload) values(?,?,?,?,?)";
	public final static String select_normal_user = "select uid,u_name,u_type,u_upload from tc_user where u_mail = ? and u_pass = ?";
	public final static String delete_normal_user = "delete from tc_user where uid = ?";
	public final static String update_normal_user_upload = "update tc_user set u_upload = ? where uid = ?";
	public final static String update_normal_user_pass = "update tc_user set u_pass = ? where uid = ?";
	public final static String selec_all_users = "select uid,u_name,u_mail,u_pass,u_type,u_upload from tc_user";
	public final static String select_user_byid = "select uid,u_name,u_mail,u_pass,u_type,u_upload from tc_user where uid = ?";

	// project
	public final static String add_proj = "insert into tc_proj(p_name,p_code,p_svn) values (?,?,?)";
	public final static String del_proj = "delete from tc_proj where pid = ?";
	public final static String get_all_projects = "select pid,p_name,p_code,p_svn from tc_proj";

	// project map to the user
	public final static String add_map = "insert into tc_upmap (uid,pid) values(?,?)";
	public final static String del_map_byuid = "delete from tc_upmap where uid =?";
	public final static String getProjectByUid = "select p.pid,p.p_name ,p.p_code from tc_proj p , tc_upmap t where t.uid= ? and t.pid = p.pid";

	// tcov
	public final static String add_cov = "insert into tc_cov(pid,c_dir,c_version,uploader) values(?,?,?,?)";
	public final static String get_cov_by_pid = "select c_dir,c_version,uploader,upload_time from tc_cov where pid = ? order by upload_time desc";
}
